package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IPersonExtSubmitDao;
import com.boss.salary.entity.AgencyVerify;
import com.boss.salary.entity.PersonExt;
import com.boss.salary.entity.SalaryBankPayBack;
import com.boss.sys.utils.SysUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.lang.reflect.InvocationTargetException;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

@Repository
public class PersonExtSubmitDaoImpl  extends BaseDaoImpl implements IPersonExtSubmitDao {
    @Override
    public void batchSavePersonExtSubmit(List<PersonExt> personExts) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        if(personExts.size()>0) {
            List<Object[]> paramList = new ArrayList<>(personExts.size());
            for (PersonExt personExt : personExts) {
                Object[] paramArr = {
                        SysUtils.generateGuid(), personExt.getCreateTime(), personExt.getUpdateTime(), personExt.getPersonId(),
                        personExt.getPersonAddress(), personExt.getAgencyId(), personExt.getDeptId(), personExt.getAgencyCode(),
                        personExt.getIdenNo(), personExt.getState(),//20210304新增
                        personExt.getEnterAgencyDate(), personExt.getRetireDate(), personExt.getPersonTypeCode(),
                        personExt.getPersonIdeCode(), personExt.getEndDate(), personExt.getDeadDate(), personExt.getRemarks(),
                        personExt.getSalaCardNo(), personExt.getSalaCardBank(), personExt.getGovCredCardNo(),
                        personExt.getGovCredCardBank(), personExt.getStartDate(), personExt.getMeritPayCode(),
                        personExt.getSchRecCode(), personExt.getPosCode(), personExt.getGrCode(), personExt.getTecGrCode(),
                        personExt.getIsAuth(), personExt.getSalaGrCode(), personExt.getPersonState(), personExt.getChangeState(),
                        personExt.getVerifyState(), personExt.getPerSourceCode(), personExt.getIsSpecialPost(), personExt.getIsInsurance(),
                        personExt.getIsUniSala(), personExt.getIsStopSalary(), personExt.getIsDeleted(), personExt.getMofDivCode(),
                        personExt.getPersonTypeId(), personExt.getPostId(), personExt.getGradeId(), personExt.getLevelId(), personExt.getPersonName(),
                        personExt.getShowOrder(), personExt.getPhone(), personExt.getPersonnelCode(),
                        personExt.getFuncId(),personExt.getFuncName(),personExt.getFuncCode()
                };
                List<Object> params = new ArrayList<>(Arrays.asList(paramArr));

                paramList.add(params.toArray());
            }
            StringBuilder sql = new StringBuilder();
            sql.append("insert into ").append(TableName.PERSON_EXT_SUBMIT);
            sql.append("(id, create_time, update_time, person_id, person_address, agency_id, dept_id, agency_code, iden_no,state, enter_agency_date,")
                    .append("retire_date, person_type_code, person_ide_code, end_date, dead_date, remarks, sala_card_no,")
                    .append("sala_card_bank, gov_cred_card_no, gov_cred_card_bank, start_date, merit_pay_code,")
                    .append("sch_rec_code, pos_code, gr_code, tec_gr_code, is_auth, sala_gr_code, person_state,")
                    .append("change_state, verify_state, per_source_code, is_special_post, is_insurance, is_uni_sala, is_stop_salary, is_deleted,mof_div_code, ")
                    .append(" person_type_id, post_id, grade_id, level_id,person_name,show_order,phone,personnel_code,func_id,func_name,func_code )");
            sql.append("values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");

            super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
                public int getBatchSize() {
                    return paramList.size();
                    //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
                }
                public void setValues(PreparedStatement ps, int i)throws SQLException {
                    Object[] sme = paramList.get(i);
                    for(int a = 0; a < 50; a++){
                        ps.setObject(a+1,sme[a]);
                    }

                }
            });
        }
    }

    @Override
    public void batchPhysicalDeletePersonExtSubmit(String province, List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        sb.append(" delete from ").append(TableName.PERSON_EXT_SUBMIT)
                .append(" where ").append(" mof_div_code = ? ");
        if(!CollectionUtils.isEmpty(agencyIds)){
            sb.append(" and ").append(SysUtils.inSql("agency_id", agencyIds));
        }
        super.getjdbcTemplate().update(sb.toString(), province);
        //return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper<>(AgencyInfo.class),userDTO.getProvince())
    }

    @Override
    public void updateSalaCardNoByIdenNo(SalaryBankPayBack salaryBankPayBack) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.PERSON_EXT_SUBMIT).append(" pe ")
                .append(" set pe.sala_card_no = ?,pe.person_name=?,pe.iden_no=? ").append(" where pe.person_id = ? ");
        Object[] params = new Object[]{
                salaryBankPayBack.getNewSalaCardNo(),salaryBankPayBack.getNewPersonName(),
                salaryBankPayBack.getNewIdenNo(), salaryBankPayBack.getPersonId()
        };
        this.getJdbcTemplate().update(sql.toString(), params);
    }
//通过异常卡号的身份证号去人员基本信息上报表查信息
    @Override
    public List<Map<String, Object>> UnitOfAbnormal(List<SalaryBankPayBack> salaryBankPayBacks) {
        List<String> paramList = new ArrayList<>();
        for(SalaryBankPayBack salaryBankPayBack:salaryBankPayBacks) {
            paramList.add(salaryBankPayBack.getPersonId());
        }
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" agency_id,COUNT(*) AS cn ")
                .append(" from ");
                sql.append(TableName.PERSON_EXT_SUBMIT)
                .append(" where");
                sql.append(SysUtils.inSql("person_id",paramList))
                .append(" group by agency_id");

        return super.getJdbcTemplate().queryForList(sql.toString());


    }

    @Override
    public void updatePersonExtSubmitCode(List<AgencyVerify> updateList) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update ").append(TableName.PERSON_EXT_SUBMIT);
        sql.append(" set  agency_code=?   where agency_id=?");
        List<Object[]> paramsList = new ArrayList<>(updateList.size());
        for (AgencyVerify personExtSubmit : updateList) {
            Object[] params = new Object[]{
                    personExtSubmit.getAgencyCode(),personExtSubmit.getAgencyId()
            };
            paramsList.add(params);
        }
        super.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 2; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }
}
